﻿CREATE PROCEDURE [dbo].[yaf_user_accessmasks](
                @BoardID INT,
                @UserID  INT)
AS
    BEGIN
        SELECT   *
        FROM     (SELECT   AccessMaskID = e.AccessMaskID,
                           AccessMaskName = e.Name,
                           ForumID = f.ForumID,
                           ForumName = f.Name
                  FROM     yaf_User a
                           JOIN yaf_UserGroup b
                             ON b.UserID = a.UserID
                           JOIN yaf_Group c
                             ON c.GroupID = b.GroupID
                           JOIN yaf_ForumAccess d
                             ON d.GroupID = c.GroupID
                           JOIN yaf_AccessMask e
                             ON e.AccessMaskID = d.AccessMaskID
                           JOIN yaf_Forum f
                             ON f.ForumID = d.ForumID
                  WHERE    a.UserID = @UserID
                  AND c.BoardID = @BoardID
                  GROUP BY e.AccessMaskID,e.Name,f.ForumID,f.Name
                  UNION 
                  SELECT   AccessMaskID = c.AccessMaskID,
                           AccessMaskName = c.Name,
                           ForumID = d.ForumID,
                           ForumName = d.Name
                  FROM     yaf_User a
                           JOIN yaf_UserForum b
                             ON b.UserID = a.UserID
                           JOIN yaf_AccessMask c
                             ON c.AccessMaskID = b.AccessMaskID
                           JOIN yaf_Forum d
                             ON d.ForumID = b.ForumID
                  WHERE    a.UserID = @UserID
                  AND c.BoardID = @BoardID
                  GROUP BY c.AccessMaskID,c.Name,d.ForumID,d.Name) AS x
        ORDER BY ForumName,
                 AccessMaskName
    END


